import pandas as pd
from sqlalchemy import create_engine
import os
import re
from xpinyin import Pinyin
import json


curDir = os.path.dirname(os.path.abspath(__file__))
appDir = os.path.dirname(curDir)
baseDir = os.path.dirname(appDir)

excel_path = r'D:\E\projects\工位信息系统\django-vue3-admin20230921\backend\station_system\temp\guowangbuliang.xlsx' 

'../temp/dbsp.sqlite3'
db_path = os.path.join(appDir, 'db.sqlite3')
db_engine = create_engine(f'sqlite:///{db_path}', echo=True)





# 配置db_table名称，以及字段和excel列名之间的对应关系
table_field_col_dict = {
    'station_station': {
        'code': 'station',
        'name': '工位名称',
        'product_line': '生产线'
    },
    'station_torque': {
        'station':'station',
        'luoshuan_name' : '螺栓螺母名称',
        'part_no' : '零件号',
        'duishoujian_name' : '对手零件名称',
        'duishoujian_no' : '对手零件号',
        'shiyongshuliang' : '数量',
        'jingu_torque' : '紧固扭矩值',
        'jiance_torque' : '检测扭矩值',
        'ningjinbanshou_leixing' : '拧紧扳手类型',
        'banshou_pinpaixinghao' : '扳手品牌型号',
        'dingniubanshou_pinpai' : '定扭力扳手品牌',
        'dingniulibanshou_xinghao' : '定扭力扳手型号',
        'beizhu':'备注'
    },
    'station_guowangbuliang': {
        'station':'station',
        'wentifenlei' : '问题分类',
        'PCR_danhao' : 'PCR单号',
        'wentimiaoshu' : '问题描述',
        'jutibumen' : '具体部门',
        'zerenren' : '责任人',
        'genjinren' : '跟进人',
        'genbenyuanyinfenxi' : '根本原因分析',
        'duanqijiejuecuoshi' : '短期解决措施',
        'changqijiejuecuoshi' : '长期解决措施',
        'jinzhanjihua' : '进展计划',
        'duandianhao' : '断点号',
        'guanbishijian' : '关闭时间',
        'beizhu':'备注'
    },
        'station_jiajutiaozhengtaizhang': {
        'station':'station',
        'NO':'NO',
        'quyu':'区域',
        'tiaozhengneirong':'调整内容',
        'tiaozhengduandian':'调整断点',
        'shifoufuyuan':'是否复原',
        'tiaozhengriqi':'调整日期',
        'jilubianhao':'记录编号',
        'beizhu':'备注'
    },
    "station_pbom":
    {

        'shun_xu_hao':    'shun_xu_hao',
        'IA':    'IA',
        'IA_ying_wen_ming_cheng':    'IA_ying_wen_ming_cheng',
        'IA_zhong_wen_ming_cheng':    'IA_zhong_wen_ming_cheng',
        'ji_bie':    'ji_bie',
        'ling_bu_jian_hao':    'ling_bu_jian_hao',
        'ling_bu_jian_zhong_wen_ming_cheng':    'ling_bu_jian_zhong_wen_ming_cheng',
        'ling_bu_jian_ying_wen_ming_cheng':    'ling_bu_jian_ying_wen_ming_cheng',
        'dan_wei_yong_liang':    'dan_wei_yong_liang',
        'mo_kuai':    'mo_kuai',
        'mo_kuai_zhong_wen_miao_shu' :   'mo_kuai_zhong_wen_miao_shu',
        'ben_se_jian_biao_shi':    'ben_se_jian_biao_shi',
        'yan_se_jian_biao_shi':    'yan_se_jian_biao_shi',
        'VPPS':    'VPPS',
        'VPPS_ying_wen_ming_cheng':    'VPPS_ying_wen_ming_cheng',
        'VPPS_zhong_wen_ming_cheng':    'VPPS_zhong_wen_ming_cheng',
        'FNA':    'FNA',
        'FNA_zhong_wen_miao_shu':    'FNA_zhong_wen_miao_shu',
        'FNA_ying_wen_miao_shu':    'FNA_ying_wen_miao_shu',
        'fu_ji':    'fu_ji',
        'abc_00_ji':    'abc_00_ji',
        'pai_xu_zi_fu_chuan':    'pai_xu_zi_fu_chuan',
        'jin_gu_li_ju':    'jin_gu_li_ju',
        'li_ju_zhong_yao_du':    'li_ju_zhong_yao_du',
        'huo_yuan':    'huo_yuan',
        'huo_yuan_miao_shu':    'huo_yuan_miao_shu',
        'jie_gou_huo_yuan':    'jie_gou_huo_yuan',
        'jie_gou_huo_yuan_miao_shu':    'jie_gou_huo_yuan_miao_shu',
        'cai_gou_dan_yuan_biao_shi':    'cai_gou_dan_yuan_biao_shi',
        'shou_hou_jian_biao_shi':    'shou_hou_jian_biao_shi',
        'shou_yong_che_xing':    'shou_yong_che_xing',
        'du_liang_dan_wei':    'du_liang_dan_wei',
        'ling_jian_zhi_liang_kg_dan_wei':    'ling_jian_zhi_liang_kg_dan_wei',
        'gong_wei':    'gong_wei',
        'cai_liao':    'cai_liao',
        'liao_hou_mm':    'liao_hou_mm',
        'dui_cheng_jian_biao_shi':    'dui_cheng_jian_biao_shi',
        'dui_cheng_jian_hao':    'dui_cheng_jian_hao',
        'ling_jian_lei_xing':    'ling_jian_lei_xing',
        'ling_jian_zhong_yao_du':    'ling_jian_zhong_yao_du',
        'fa_gui_jian_biao_shi':    'fa_gui_jian_biao_shi',
        'ruan_jian_ban_ben':    'ruan_jian_ban_ben',
        'ying_jian_ban_ben':    'ying_jian_ban_ben',
        'kong_zhi_qi_lei_xing':    'kong_zhi_qi_lei_xing',
        'shi_fou_zai_xian_shua_xie':    'shi_fou_zai_xian_shua_xie',
        'shua_xie_yu':    'shua_xie_yu',
        'ruan_jian_sheng_chan_ban_ben':    'ruan_jian_sheng_chan_ban_ben',
        'ying_jian_sheng_chan_ban_ben':    'ying_jian_sheng_chan_ban_ben',
        'ling_jian_fen_lei':    'ling_jian_fen_lei',
        'MODULE_ID':    'MODULE_ID',
        'kai_fa_zhou_qi':    'kai_fa_zhou_qi',
        'shu_mo_bian_hao':    'shu_mo_bian_hao',
        'shu_mo_ban_ben':    'shu_mo_ban_ben',
        'shu_mo_fa_bu_shi_jian':    'shu_mo_fa_bu_shi_jian',
        'tu_zhi_hao':    'tu_zhi_hao',
        'tu_zhi_ban_ben':    'tu_zhi_ban_ben',
        'tu_zhi_fa_bu_shi_jian':    'tu_zhi_fa_bu_shi_jian',
        'SOR_bian_hao':    'SOR_bian_hao',
        'SOR_fa_bu_shi_jian':    'SOR_fa_bu_shi_jian',
        'ESO_bian_hao':    'ESO_bian_hao',
        'ESO_pi_zhun_shi_jian':    'ESO_pi_zhun_shi_jian',
        'ling_bu_jian_you_xiao_xing':    'ling_bu_jian_you_xiao_xing',
        'ze_ren_gong_cheng_shi':    'ze_ren_gong_cheng_shi',
        'ze_ren_gong_cheng_shi_gong_hao':    'ze_ren_gong_cheng_shi_gong_hao',
        'ze_ren_zhuan_ye':    'ze_ren_zhuan_ye',
        'bei_zhu':    'bei_zhu',
        'PCN':    'PCN',
        'PCN_sheng_xiao_ri_qi':    'PCN_sheng_xiao_ri_qi',
        'ET':    'ET',
        'sheng_xiao_ri_qi':    'sheng_xiao_ri_qi',
        'shi_xiao_ri_qi':    'shi_xiao_ri_qi',
        'jian_yi_sheng_xiao_ri_qi':    'jian_yi_sheng_xiao_ri_qi',
        'jian_yi_shi_xiao_ri_qi':    'jian_yi_shi_xiao_ri_qi'
    }
    }

class ExcelToDb:
    def __init__(self, db_engine=None):
        self.engine = db_engine
        # 数据库字段名与excel列名的对应关系,key:字段名，值：列名
        self.col_name_map = {}

    def excel_to_db(self, df, model):
        
        # 删除原表中所有数据
        model.objects.all().delete()

        # 获取模型的db_table名称
        db_table = model._meta.db_table

        # 从配置字典中获取字段名和列名的对应关系
        # field_col_dict = table_field_col_dict.get(db_table, None)
        field_col_dict = self.col_name_map

        ####################################
        # FIXME: 
        #     pandas转化为dict-----df.to_dict(参数):
        #     默认参数:"dict":列标题作为外层dict键值 , 索引(行标题)作为内层dict键值
        #     参数"list":列标题是外层dict键值,内层是list,没有了行标题
        #     参数"split":{"index":[],"columns":[],"data":[[]]},将index\columns分开来
        #     参数"recods":外层是列表,内层是列标题为键值的列表
        #     参数"index":与参数dict相反,行标题作为外层dict的键值,列标题作为内层dict键值
        #     版权声明：本文为CSDN博主「卡卡卡骨」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
        #     原文链接：https://blog.csdn.net/woysihsb/article/details/122126580
        ####################################

        # 两层字典
        # 外层字典，行号索引为键，每一行数据的字典为值
        # 内存字典，列名为键，每一列数据为值
        df_dict = df.to_dict(orient='index')


        # 遍历df_dict，将每一行数据转换为model对象，然后保存到数据库
        for row in df_dict.values():
            model.objects.create(**row)

    def handle_col_name(self, col_name):
        p = Pinyin()
        pass
        # 清理前后的空格
        col_name_1 = col_name.strip()

        # 转换中文为拼音
        col_name_2 = p.get_pinyin(col_name_1, '_')

        # 清理特殊字符
        col_name_3 = re.sub(r'[^a-zA-Z0-9_]', '', col_name_2)

        # 预留功能
        col_name_4 = col_name_3

        # 数字开头的字段名，前面加上_
        col_name_5 = re.sub(r'^\d.*', f'num_{col_name_4}', col_name_4)

        return col_name_5

    def read_excel(self, excel_path=excel_path, **kwargs):
        df = pd.read_excel(excel_path, **kwargs)
        col_dict = {}
        # 清理字段名中的空格、换行符等
        # col = col.strip().replace('\n', '',).replace('\r','')
        df.rename(columns=lambda col: col.strip().replace('\n','').replace('\r',''))

        # 先把col_name_map初始化，避免重复读取excel时候，重复添加
        self.col_name_map = {}
        for col in df.columns:

            # 处理字段名称
            col_dict[col] = self.handle_col_name(col)

            # 转换后的字段名和原字段名的对应关系
            self.col_name_map[col_dict[col]] = col

        df.rename(columns=col_dict, inplace=True)

        return df
    
    def export_col_name_map(self, path):
        # 将字段名和列名的对应关系导出到json文件
        json.dump(self.col_name_map, open(path, 'w', encoding='utf-8'), indent=4)
    
    def generate_model_code(self, path):
        with open(path, 'w', encoding='utf-8') as f:
            for (key, value) in self.col_name_map.items():
                print(key, value)
                f.write(f'{key} = models.TextField(verbose_name="{value}", blank=True, null=True)\n')


def main():
    e2d = ExcelToDb(db_engine=db_engine)
    df = e2d.read_excel()
    print(df)
    # e2d.excel_to_db(df, Station)

    for row in df.itertuples():
        # print(getattr(row, 'station'),getattr(row, '工位名称'),getattr(row, '生产线'))
        print(row)
        print('--------------')

if __name__ == '__main__':
    main()